[アップデート] Aurora PostgreSQLで利用可能な拡張機能がふえました

Takuya Shibata

2021.06.25

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

しばたです。

少し前のはなしですがAurora PostgreSQLにおいて利用可能な拡張機能が一気に増えました。
AWSからのアナウンスは以下となります。

追加された拡張機能

今回追加された拡張機能は以下の4つです。
元々RDS for PostgreSQLでサポートされていた拡張が今月になってAuroraにもやってきた形となります。

pg_proctab : OSのプロセス情報にアクセスするためのストアドファンクション群
pg_cron : データベース内でメンテナンスコマンドのスケジュールを組む
pg_partman : パーティションテーブルの作成やメンテナンスの自動化
pg_bigm : 2-gram検索による全文検索

それぞれの拡張をサポートするバージョンは以下のドキュメントに記載されています。

Extension versions for Amazon Aurora PostgreSQL

一応本日時点(2021年6月24日)の状況をリストアップするとこんな感じで、基本的にはAurora PostgreSQL 12かAurora PostgreSQL 11の最新マイナーバージョンからのサポートとなります。

拡張機能	Aurora PostgreSQL 12.x	Aurora PostgreSQL 11.x	Aurora PostgreSQL 10.x
pg_proctab (Ver.0.0.9)	Ver.12.6 ～	Ver.11.11 ～	-
pg_cron (Ver.1.3)	Ver.12.6 ～	-	-
pg_partman (Ver.4.4.0)	Ver.12.6 ～	-	-
pg_bigm (Ver.1.2)	Ver.12.6 ～	Ver.11.11 ～	-

やってみた

せっかくなので簡単にですがそれぞれの拡張を試してみます。

検証環境

今回は東京リージョンに用意したVPC上にAurora PostgreSQL 12.6の環境を用意しました。
具体的な構築手順は割愛しますが、パラメーターグループはpg_cronを扱う際に事前の仕込みが必要なため独自のものを使用しています。

パラメーターグループは以下の設定のみ変更しています。

対象	パラメーター名	設定値	備考
DBインスタンス	shared_preload_libraries	pg_stat_statements,pg_cron	pg_cronを追記
DBインスタンス	max_worker_processes	6	必ず2以上の値にし、かつ cron.max_running_jobs < max_worker_processes となる様にする

あとデータベースへの接続にはpgAdmin (+付属のpsql)を使います。

0. デフォルト状態の確認

最初にデフォルトの状態を確認します。

今回はPostgreSQL 12.6を使ってますので先述の拡張が全て利用可能な状態となっています。

mydb=> SELECT *
mydb->   FROM pg_available_extensions
mydb->  WHERE name IN ('pg_proctab', 'pg_cron', 'pg_partman', 'pg_bigm')
mydb->  ORDER BY name;
    name    | default_version | installed_version |                             comment
------------+-----------------+-------------------+------------------------------------------------------------------
 pg_bigm    | 1.2             |                   | text similarity measurement and index searching based on bigrams
 pg_cron    | 1.3             |                   | Job scheduler for PostgreSQL
 pg_partman | 4.4.0           |                   | Extension to manage partitioned tables by time or ID
 pg_proctab | 0.0.9           |                   | Access operating system process table
(4 rows)

ただし、データベースの初期状態ではどの拡張もセットアップされていません。

mydb=> SELECT * FROM pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 14299 | plpgsql |       10 |           11 | f              | 1.0        |           |
(1 row)

1. pg_proctab を試す

まずはpg_proctab拡張を試してみます。
前節の通り初期状態で利用不可なのでまずは拡張機能をインストールします。

CREATE EXTENSION pg_proctab;

結果pg_extensionビューで参照可能になっていればOKです。

mydb=> CREATE EXTENSION pg_proctab;
CREATE EXTENSION
mydb=> SELECT * FROM pg_extension;
  oid  |  extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+------------+----------+--------------+----------------+------------+-----------+--------------
 14299 | plpgsql    |       10 |           11 | f              | 1.0        |           |
 20498 | pg_proctab |       10 |         2200 | t              | 0.0.9      |           |
(2 rows)

ちょっとこの拡張に関するオフィシャルなドキュメントを見つけることができなかったのですが、GitLabにあるソースを読む限りでは以下の5つのストアドファンクションを使える様になる様です。

pg_proctab() : OSのプロセス情報を取得
pg_cputime() : OSのCPU時間を取得
pg_loadavg() : OSロードアベレージを取得
pg_memusage() : OSのメモリ使用率を取得
pg_diskusage() : OSのディスク使用率を取得

一例としてpg_loadavg()からロードアベレージを取得するとこんな感じの結果を返します。

mydb=> SELECT * FROM pg_loadavg();
 load1 | load5 | load15 | last_pid
-------+-------+--------+----------
  0.02 |  0.07 |   0.11 |     3696
(1 row)

この辺の値はAuroraのモニタリング設定(拡張モニタリングやOSプロセスリスト)からも取得できますので拡張単体ではこれといった使いどころがなさそうです。
一応AWSのアナウンスではpg_proctab()がトラブルシュートに使えるとされており、実際pg_proctab()ではモニタリング設定より詳細な情報を取得可能となっています。

2. pg_cron を試す

次にpg_cron拡張を試してみます。
この拡張の利用方法は以下のドキュメントに記載されており、ちょっと前提条件やインストール手順が特別です。

Scheduling maintenance with the PostgreSQL pg_cron extension

pg_cron拡張を利用するには事前にshared_preload_librariesパラメーターにpg_cronを追記しておく必要があります。
(今回は最初から準備しておきましたが、既存の環境を変更する場合はAuroraの再起動が必要となります)

そして例によって最初に拡張機能をインストールするのですが、この拡張はpostgresデータベースにしか作成できませんので注意してください。

CREATE EXTENSION pg_cron;

上手くいけば以下の様な感じになります。
エラーが出る場合はエラーメッセージの内容を確認し適宜対処してください。

# 最初に postgres データベースに接続しておくこと
postgres=> CREATE EXTENSION pg_cron;
CREATE EXTENSION
postgres=> SELECT * FROM pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion |         extconfig         | extcondition
-------+---------+----------+--------------+----------------+------------+---------------------------+---------------
 14299 | plpgsql |       10 |           11 | f              | 1.0        |                           |
 20505 | pg_cron |       10 |         2200 | f              | 1.3        | {20509,20507,20531,20529} | {"","","",""}
(2 rows)

この拡張ではジョブの情報はcron.job、ジョブの実行結果はcron.job_run_detailsテーブルで確認できます。
ジョブを追加したい場合はcron.schedule()ファンクションを使います。

簡単な例を出すと以下の様な感じでcron.schedule()ファンクションを実行します。

# 毎朝 9:10 (UTC) に sample_table テーブルをVACUUMするジョブ
SELECT cron.schedule('mydb manual vacuum', '10 9 * * *', 'VACUUM FREEZE sample_table');

結果この様になります。

postgres=> SELECT cron.schedule('mydb manual vacuum', '10 9 * * *', 'VACUUM FREEZE sample_table');
 schedule
----------
       37
(1 row)

postgres=> SELECT * FROM cron.job;
 jobid |  schedule  |          command           | nodename  | nodeport | database | username | active |      jobname
-------+------------+----------------------------+-----------+----------+----------+----------+--------+--------------------
    37 | 10 9 * * * | VACUUM FREEZE sample_table | localhost |     5432 | postgres | postgres | t      | mydb manual vacuum
(1 row)

この結果をよく見るとdatabase列がpostgresのままです。
Aurora(およびRDS)ではpostgresデータベースでしかこの処理を実行できないためこの様になります。

本来ジョブを実行したいデータベース(mydb)に変えるには直接UPDATE文を実行し値を変えてやります。

参考 : Scheduling a cron job for a database other than postgres

# ジョブの対象データベースを変えたい場合は直接UPDATEする
UPDATE cron.job SET database = 'mydb' WHERE jobid = <変えたいジョブのJobid>;

先ほどのジョブを変えるとこんな感じになります。

postgres=> UPDATE cron.job SET database = 'mydb' WHERE jobid = 37;
UPDATE 1
postgres=> SELECT * FROM cron.job;
 jobid |  schedule  |          command           | nodename  | nodeport | database | username | active |      jobname
-------+------------+----------------------------+-----------+----------+----------+----------+--------+--------------------
    37 | 10 9 * * * | VACUUM FREEZE sample_table | localhost |     5432 | mydb     | postgres | t      | mydb manual vacuum
(1 row)

あとはジョブが実行されるのを待ってその結果を確認してやればOKです。

postgres=> SELECT * FROM cron.job_run_details;
 jobid | runid | job_pid | database | username |          command           |  status   | return_message |          start_time           |           end_time
-------+-------+---------+----------+----------+----------------------------+-----------+----------------+-------------------------------+-------------------------------
    37 |     4 |    2366 | mydb     | postgres | VACUUM FREEZE sample_table | succeeded | VACUUM         | 2021-06-25 09:10:00.067011+00 | 2021-06-25 09:10:00.111035+00
(1 row)

ちなみにcron.job_run_detailsのレコードは自動では消えません。
以下のドキュメントを参考に古いレコードを削除するジョブも登録しておくと良いでしょう。

Cron job to purge the pg_cron history

【注意】max_worker_processes パラメーターの値について

pg_cron拡張はジョブの同時実行数がcron.max_running_jobsパラメーターで定義されており、デフォルトでcron.max_running_jobs = 5となっています。
そしてこのジョブはPostgreSQLのワーカープロセスで実行され、拡張のソースコードを読むとcron.max_running_jobsの値は

0 ～ (max_worker_processes - 1)

の間に収まっていることが要求されています。

スペックの低いインスタンスだと max_worker_processes の値が足りずデータベースの起動時などに以下の様なエラーを吐きcronジョブが実行されません。

2021-06-25 07:45:30 UTC::@:[13258]:log: 5 is outside the valid range for parameter "cron.max_running_jobs" (0 .. 0)

(最初 db.t3.medium のデフォルト設定 max_worker_processes = 1 で試した場合に出たエラー)

このため、max_worker_processesの値は必ず2以上になる様にし、かつcron.max_running_jobs < max_worker_processesとなる様にパラメーターを調整してください。

設定例 : max_worker_processes = 3, cron.max_running_jobs = 1 ^[1]

本記事では手っ取り早く動作確認するために max_worker_processes = 6, cron.max_running_jobs = 5(デフォルト値のまま)としました。
本番環境ではインスタンスのスペックに応じて

max_worker_processes
cron.max_running_jobs

の値を調整してやると良いでしょう。

3. pg_partman を試す

続けてpg_partman拡張を試します。
この拡張の利用方法は以下のドキュメントに記載されています。

Managing PostgreSQL partitions with the pg_partman extension

この拡張は専用のスキーマが必要になるため、最初にスキーマを作ってから拡張を有効にします。

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

結果はこんな感じになります。

mydb=> CREATE SCHEMA partman;
CREATE SCHEMA
mydb=> CREATE EXTENSION pg_partman WITH SCHEMA partman;
CREATE EXTENSION

これで準備ができたので、あとは上記ドキュメントにあるサンプルを実行してみます。
はじめにテスト用スキーマとテーブルを用意しします。

# テストスキーマを用意
CREATE SCHEMA data_mart;
# テストテーブルを用意
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
        org_name TEXT,
        CONSTRAINT pk_organization PRIMARY KEY (org_id)  
    );

CREATE TABLE data_mart.events(
        event_id        BIGSERIAL, 
        operation       CHAR(1), 
        value           FLOAT(24), 
        parent_event_id BIGINT, 
        event_type      VARCHAR(25), 
        org_id          BIGSERIAL, 
        created_at      timestamp, 
        CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), 
        CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), 
        CONSTRAINT fk_orga_membership 
            FOREIGN KEY(org_id) 
            REFERENCES data_mart.organization (org_id),
        CONSTRAINT fk_parent_event_id 
            FOREIGN KEY(parent_event_id, created_at) 
            REFERENCES data_mart.events (event_id,created_at)
    ) PARTITION BY RANGE (created_at);

CREATE INDEX idx_org_id     ON  data_mart.events(org_id);
CREATE INDEX idx_event_type ON  data_mart.events(event_type);

create_parent()ファンクションを使うと指定テーブルのパーティション設定を実施できます。
こちらもドキュメントのサンプルをそのまま試してみます。

# data_mart.events テーブルの created_at 列をパーティショニングする
SELECT partman.create_parent( p_parent_table => 'data_mart.events',
 p_control => 'created_at',
 p_type => 'native',
 p_interval=> 'daily',
 p_premake => 30);

結果はこんな感じになり、

mydb=> SELECT partman.create_parent( p_parent_table => 'data_mart.events',
mydb(>  p_control => 'created_at',
mydb(>  p_type => 'native',
mydb(>  p_interval=> 'daily',
mydb(>  p_premake => 30);
 create_parent
---------------
 t
(1 row)

data_mart.eventsテーブルを確認するといい感じにパーティションが出来上がっています。

4. pg_bigm を試す

最後にpg_bigm拡張を試します。
こちらは以前RDS for PostgreSQLでやった手順とまったく同じです。

今回は拡張を利用可能にするところまで紹介します。

CREATE EXTENSION pg_bigm;

結果はこんな感じ。

mydb=> CREATE EXTENSION pg_bigm;
CREATE EXTENSION
mydb=> SELECT * FROM pg_extension;
  oid  |  extname   | extowner | extnamespace | extrelocatable | extversion |      extconfig      | extcondition
-------+------------+----------+--------------+----------------+------------+---------------------+--------------
 14299 | plpgsql    |       10 |           11 | f              | 1.0        |                     |
 20498 | pg_proctab |       10 |         2200 | t              | 0.0.9      |                     |
 24587 | pg_partman |       10 |        24586 | f              | 4.4.0      | {24591,24619,24652} | {"","",""}
 24737 | pg_bigm    |       10 |         2200 | t              | 1.2        |                     |
(4 rows)

あとは前掲の記事の通り、テーブルに全文検索インデックスを張ってからLIKE検索をしてください。

最後に

以上となります。

ちょっとpg_cronを試した際にハマってしまったのですがなんとかすべての拡張を試すことができました。

元々RDS for PostgreSQLでサポートされていた拡張ですのでAurora PostgreSQLにも欲しいと思っていた方もいたことでしょう。
今回の更新を契機にAuroraで試していくと良いと思います。

脚注

max_worker_processes = 2 だと謎の起動エラー (could not start background process; more details may be available in the server log と言いつつサーバーログが何も出ない...) が出たりしたので max_worker_processes が低すぎるのも良くない様です... ↩︎